package android.tusnotas.model;

import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.tusnotas.activities.R;
import android.tusnotas.beans.Course;
import android.tusnotas.beans.File;
import android.tusnotas.beans.Folder;
import android.tusnotas.beans.Grade;
import android.tusnotas.beans.Instance;
import android.tusnotas.beans.News;
import android.tusnotas.beans.Property;
import android.tusnotas.util.Properties;
import android.util.Log;

public class TusNotasModel {

//GENERAL ATTRS	
	private static TusNotasModel tusNotasModel;
	private InstanceModel instanceModel = new InstanceModel();
	private CourseModel courseModel = new CourseModel();
	private GradeModel gradeModel = new GradeModel();
	private PropertyModel propertyModel = new PropertyModel();
	private NewsModel newsModel = new NewsModel();
	private FolderModel folderModel = new FolderModel();
	private FileModel fileModel = new FileModel();
	private Context context;

//DATABASE PARAMS	
	private static final String DATABASE_NAME = "tusnotas.db";
	private static final int DATABASE_VERSION = 30;
	public static SQLiteDatabase db;

//INSTANCE TABLE PARAMS	
	public static final String INSTANCE_TABLE_NAME = "instance";
	public static SQLiteStatement intanceInsertStmt;
	public static final String INSTANCE_INSERT = "insert into " + INSTANCE_TABLE_NAME
			+ "(description) values (?)";

//COURSE TABLE PARAMS
	public static final String COURSE_TABLE_NAME = "course";
	public static SQLiteStatement courseInsertStmt;
	public static final String COURSE_INSERT = "insert into " + COURSE_TABLE_NAME
			+ "(name, classroom, instanceId, days, timetable, examenDate, idAula, attendance) values (?, ?, ?, ?, ?, ?, ?, ?)";

//GRADE TABLE PARAMS
	public static final String GRADE_TABLE_NAME = "grade";
	public static SQLiteStatement gradeInsertStmt;
	public static final String GRADE_INSERT = "insert into " + GRADE_TABLE_NAME
			+ "(activity, grade, courseId) values (?, ?, ?)";
	
//PROPS TABLE PARAMS
	public static final String PROPERTY_TABLE_NAME = "property";
	public static SQLiteStatement propertyInsertStmt;
	public static final String PROPERTY_INSERT = "insert into " + PROPERTY_TABLE_NAME
			+ "(key, value) values (?, ?)";
	
//NEWS TABLE PARAMS
	public static final String NEWS_TABLE_NAME = "news";
	public static SQLiteStatement newsInsertStmt;
	public static final String NEWS_INSERT = "insert into " + NEWS_TABLE_NAME
	+ "(content, courseId) values (?, ?)";

//FOLDERS TABLE PARAMS
	public static final String FOLDER_TABLE_NAME = "folder";
	public static SQLiteStatement folderInsertStmt;
	public static final String FOLDER_INSERT = "insert into " + FOLDER_TABLE_NAME
	+ "(name,courseId) values (?,?)";

//FILES TABLE PARAMS
	public static final String FILE_TABLE_NAME = "file";
	public static SQLiteStatement fileInsertStmt;
	public static final String FILE_INSERT = "insert into " + FILE_TABLE_NAME
	+ "(name,link,extension,size,date,folderId) values (?,?,?,?,?,?)";
	
	//SINGLETON INITIALIZATION
	public static TusNotasModel getTusNotasModel (Context context){
		if (tusNotasModel == null){
			tusNotasModel = new TusNotasModel(context);
		}
		return tusNotasModel;
	}
	
	private TusNotasModel(Context context) {
		this.context = context;
		OpenHelper openHelper = new OpenHelper(this.context);
		db = openHelper.getWritableDatabase();		
		
		intanceInsertStmt = db.compileStatement(INSTANCE_INSERT);
		courseInsertStmt = db.compileStatement(COURSE_INSERT);
		gradeInsertStmt = db.compileStatement(GRADE_INSERT);
		propertyInsertStmt = db.compileStatement(PROPERTY_INSERT);
		newsInsertStmt = db.compileStatement(NEWS_INSERT);
		folderInsertStmt = db.compileStatement(FOLDER_INSERT);
		fileInsertStmt = db.compileStatement(FILE_INSERT);
	}
//END SINGLETON
	
	
//INSERT STATEMENTS	
	public long insertInstance(String description) {
		return instanceModel.insertInstance(description);
	}
	
	public long insertInstance(Instance instance) {
		return instanceModel.insertInstance(instance);
	}
	
	public long insertCourse(String name, String classroom, Integer instanceId, String days, String timetable, String examenDate, String idAula, String attendance) {
		return courseModel.insertCourse(name, classroom, instanceId, days, timetable, examenDate, idAula, attendance);
	}
	
	public long insertCourse(Course course) {
		return courseModel.insertCourse(course);
	}
	
	public long insertGrade(String activity, String grade, Integer courseId) {
		return gradeModel.insertGrade(activity, grade, courseId);
	}
	
	public long insertGrade(Grade grade) {
		return gradeModel.insertGrade(grade);
	}
	
	public long insertProperty(String key, String value) {
		return propertyModel.insertProperty(key, value);
	}
	
	public long insertProperty(Property property) {
		return propertyModel.insertProperty(property);
	}
	
	public long insertNews(String content, Integer courseId) {
		return newsModel.insertNews(content, courseId);
	}
	
	public long insertNews(News news) {
		return newsModel.insertNews(news);
	}
	
	public long insertFolder(Folder folder, Integer courseId){
		return folderModel.insertFolder(folder,courseId);
	}
	
	public long insertFile(File file, Integer folderId){
		return fileModel.insertFile(file,folderId);
	}
	
//END INSERTS
	

//UPDATE STATEMENTS
	public void updateProperty(String key, String value){
		propertyModel.updateProperty(key, value);
	}
	
	public void updateProperty(Property property){
		propertyModel.updateProperty(property);
	}
	
	public void updateGrade(Integer id, String grade){
		gradeModel.updateGrade(id, grade);
	}
	
	public void updateGrade(Grade grade){
		gradeModel.updateGrade(grade);
	}

	public void updateNews(Integer id, String content){
		newsModel.updateNews(id, content);
	}
	
	public void updateNews(News news){
		newsModel.updateNews(news);
	}
//END UPDATES
	
	
//DELETE STATEMENTS
	public void deleteAllInstances() {
		instanceModel.deleteAllInstances();
	}
	
	public void deleteInstanceById(Integer id) {
		instanceModel.deleteInstanceById(id);
	}

	public void deleteAllCourses() {
		courseModel.deleteAllCourses();
	}
	
	public void deleteCourseById(Integer id) {
		courseModel.deleteCourseById(id);
	}
	
	public void deleteAllGrades() {
		gradeModel.deleteAllGrades();
	}
	
	public void deleteGradeById(Integer id) {
		gradeModel.deleteGradeById(id);
	}
	
	public void deleteAllProperties() {
		propertyModel.deleteAllProperties();
	}
	
	public void deletePropertyById(Integer id) {
		propertyModel.deletePropertyById(id);
	}
	
	public void deletePropertyByKey(String key) {
		propertyModel.deletePropertyByKey(key);
	}
	
	public void deleteAllNews() {
		newsModel.deleteAllNews();
	}
	
	public void deleteNewsById(Integer id) {
		newsModel.deleteNewsById(id);
	}

//END DELETES
	
	
//SELECT STATEMENTS
	public List<Instance> selectAllInstances() {
		return instanceModel.selectAllInstances();
	}
	
	public Instance selectInstanceByDescription(String description) {
		return instanceModel.selectInstanceByDescription(description);
	}
	
	public Instance selectInstanceById(Integer id) {
		return instanceModel.selectInstanceById(id);
	}
	
	public List<Course> selectAllCourses() {
		return courseModel.selectAllCourses();
	}
	
	public List<Course> selectCoursesByInstance(Integer instanceId) {
		return courseModel.selectCoursesByInstance(instanceId);
	}
	
	public Course selectCoursesById(Integer courseId) {
		return courseModel.selectCourseById(courseId);
	}
	
	public Course selectCourseByInstanceAndName(Integer instanceId, String name) {
		return courseModel.selectCourseByInstanceAndName(instanceId, name);
	}
	
	public Course selectCourseByIdAula(Integer idAula) {
		return courseModel.selectCourseByIdAula(idAula);
	}
	
	public List<Grade> selectAllGrades() {
		return gradeModel.selectAllGrades();
	}
	
	public List<Grade> selectGradesByCourse(Integer courseId) {
		return gradeModel.selectGradesByCourse(courseId);
	}
	
	public Grade selectGradesByCourseAndActivity(Integer courseId, String activity) {
		return gradeModel.selectGradesByCourseAndActivity(courseId, activity);
	}
	
	public List<Property> selectPropertiesByInstance() {
		return propertyModel.selectAllProperties();
	}
	
	public Property selectPropertyByKey(String key) {
		return propertyModel.selectProperyByKey(key);
	}
	
	public List<News> selectAllnews() {
		return newsModel.selectAllNews();
	}
	
	public List<News> selectNewsByCourse(Integer courseId) {
		return newsModel.selectNewsByCourse(courseId);
	}
	
	public List<Folder> selectFoldersByCourse(Integer courseId){
		return folderModel.selectAllFoldersByCourse(courseId); 
	}
	
	public List<File> selectAllFilesByFolder(Integer folderId){
		return fileModel.selectAllFilesByFolder(folderId); 
	}
	
//END SELECTS

	
//DB STARTUP. IT HANDLES DB VERSIONING	
	private static class OpenHelper extends SQLiteOpenHelper {

		private static Context myContext;
		
		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
//			onUpgrade(db, 0, 0);
			myContext = context;
		}
		
		public void insertProperties(SQLiteDatabase db){
			
			Resources res = myContext.getResources();
			
			ContentValues contentValues = new ContentValues();
			contentValues.put("key", Properties.USERNAME);
			contentValues.put("value", "");
			db.insert(TusNotasModel.PROPERTY_TABLE_NAME, null, contentValues);
			
			contentValues.clear();
			contentValues.put("key", Properties.PASSWORD);
			contentValues.put("value", "");
			db.insert(TusNotasModel.PROPERTY_TABLE_NAME, null, contentValues);

			contentValues.clear();
			contentValues.put("key", Properties.HASH);
			contentValues.put("value", "");
			db.insert(TusNotasModel.PROPERTY_TABLE_NAME, null, contentValues);
			
			contentValues.clear();
			contentValues.put("key", Properties.FREQUENCY);
			String frequencyIndex = res.getString(R.string.default_frequency);
			contentValues.put("value", frequencyIndex);
			db.insert(TusNotasModel.PROPERTY_TABLE_NAME, null, contentValues);
			
			contentValues.clear();
			contentValues.put("key", Properties.NOTIF_LEVEL);
			String notifLevelIndex = res.getString(R.string.default_notif_level);
			contentValues.put("value", notifLevelIndex);
			db.insert(TusNotasModel.PROPERTY_TABLE_NAME, null, contentValues);
			
			contentValues.clear();
			contentValues.put("key", Properties.NUM_WRONG_PASS);
			contentValues.put("value", 0);
			db.insert(TusNotasModel.PROPERTY_TABLE_NAME, null, contentValues);
			
			contentValues.clear();
			contentValues.put("key", Properties.PUSH_FREQUENCY);
			contentValues.put("value", 600000);
			db.insert(TusNotasModel.PROPERTY_TABLE_NAME, null, contentValues);
			
			contentValues.clear();
			contentValues.put("key", Properties.PUSH_SUBSCRIBED);
			contentValues.put("value", "false");
			db.insert(TusNotasModel.PROPERTY_TABLE_NAME, null, contentValues);
			
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			//db.execSQL("CREATE DATABASE " + DATABASE_NAME);
			db.execSQL("CREATE TABLE " + INSTANCE_TABLE_NAME + "(id INTEGER PRIMARY KEY, description TEXT)");
			db.execSQL("CREATE TABLE " + COURSE_TABLE_NAME + "(id INTEGER PRIMARY KEY, name TEXT, classroom TEXT, instanceId INTEGER, days TEXT, timetable TEXT, examenDate TEXT, idAula TEXT, attendance TEXT)");
			db.execSQL("CREATE TABLE " + GRADE_TABLE_NAME + "(id INTEGER PRIMARY KEY, activity TEXT, grade TEXT, courseId INTEGER)");
			db.execSQL("CREATE TABLE " + PROPERTY_TABLE_NAME + "(id INTEGER PRIMARY KEY, key TEXT, value TEXT)");
			db.execSQL("CREATE TABLE " + NEWS_TABLE_NAME + "(id INTEGER PRIMARY KEY, content TEXT, courseId INTEGER)");
			db.execSQL("CREATE TABLE " + FOLDER_TABLE_NAME + "(id INTEGER PRIMARY KEY, name TEXT, courseId INTEGER)");
			db.execSQL("CREATE TABLE " + FILE_TABLE_NAME + "(id INTEGER PRIMARY KEY, name TEXT, link TEXT, extension TEXT," +
					"size INTEGER, date TEXT,folderId INTEGER)");
			
			insertProperties(db);
			
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(this.getClass().getName(), "Upgrading database, this will drop tables and recreate.");
			db.execSQL("DROP TABLE IF EXISTS " + INSTANCE_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + COURSE_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + GRADE_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + PROPERTY_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + NEWS_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + FOLDER_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + FILE_TABLE_NAME);
			onCreate(db);
		}
	}
}